﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
   public class CompanyHistoryUtility:BaseUtility
    {
        public CompanyHistoryUtility(DataManager manager)
            : base(manager)
        { }

    



        public int Insert( //define placeholders for the values in the table to insert
          int companyId,
          string name,
          string phone,
          string fax,
          Nullable<DateTime> relationStart,
          Nullable<DateTime> relationEnd,
          int companyTypeId,
          int clientTypeId,
          Nullable<DateTime> updateTime
       )
        {

            int nRtn = -1;
            //bool includeEndDate = relationEnd.CompareTo(DateTime.MinValue) != 0;
            StringBuilder sb = new StringBuilder();
            string commandString = "";

            commandString = "Insert INTO ddlCompanyHistory " +
                                 "(companyId,name, phone, fax,  " +
                                 "  relationStart,companyTypeId, clientTypeId,";

            //if (includeEndDate)
                commandString += " relationEnd,";

            commandString += " updateDate ) " +
                               " VALUES(@companyId,@name, @phone, @fax, @relationStart,@companyTypeId,@clientTypeId, ";

            //if (includeEndDate)
                commandString += " @relationEnd,";

            commandString += " @updateDate) " +
                               " SELECT @pResult =@@IDENTITY";



            using (var cmd = this.Factory.GetCommand(commandString))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 10, phone);
                this.Factory.AddParameter(cmd, "@fax", SqlDbType.NVarChar, 100, fax);           
                this.Factory.AddParameter(cmd, "@companyTypeId", SqlDbType.Int, 100, companyTypeId);
                this.Factory.AddParameter(cmd, "@clientTypeId", SqlDbType.Int, 10, clientTypeId);

              
               // this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.SmallDateTime, 10, relationEnd);

             

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;




                if ((relationStart == null) || (relationStart.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, relationStart);

                if ((relationEnd == null) || (relationEnd.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, relationEnd);


                if ((updateTime == null) || (updateTime.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 10, updateTime);

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;
        }


        public void Delete(int nContactID) //delete the row of data using the id passed to the function
        {
        string sql= "DELETE FROM ddlCompanyHistory " +
                "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nContactID);
                this.Factory.ExecuteNonQuery(cmd);
            }


        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM ddlCompanyHistory ";
            this.Factory.ExecuteTable(sql);
        }

        public List<CompanyHistoryViewModel> GetAll(int companyId)
        {
          string sql  = "select ct.description as companyType, dct.clientTypeCode as clientType, cp.* from ddlCompanyHistory cp " +
                " LEFT JOIN ddlcontactTypes ct ON ct.id = cp.companyTypeId" +
                " LEFT JOIN ddlClientType dct ON dct.id = cp.clientTypeId WHERE cp.companyId = " + companyId;

            return this.Factory.ExecuteObjects<CompanyHistoryViewModel>(sql);
        }


        public int Update( //define placeholders for the values in the table to insert
             int id,
             int companyId,
             string name,
             string phone,
             string fax,
             Nullable<DateTime> relationStart,
             Nullable<DateTime> relationEnd,
             int companyTypeId,
             int clientTypeId,
             Nullable<DateTime> updateTime
          )
        {

            int nRtn = -1;
          //  bool includeEndDate = relationEnd.CompareTo(DateTime.MinValue) != 0;
            StringBuilder sb = new StringBuilder();
                     

           string commandString = "UPDATE ddlCompanyHistory " +
                               "companyId=@companyId,name=@name, phone=@phone, fax=@fax,  " +
                               "  relationStart=@relationStart,companyTypeId=@companyTypeId, " +
                               "clientTypeId=@clientTypeId,";

        
                commandString += " relationEnd=@relationEnd,";
            commandString = commandString + "updateDate=@updateDate WHERE id=" + id;




            using (var cmd = this.Factory.GetCommand(commandString))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 10, phone);
                this.Factory.AddParameter(cmd, "@fax", SqlDbType.NVarChar, 100, fax);
           
                this.Factory.AddParameter(cmd, "@companyTypeId", SqlDbType.Int, 100, companyTypeId);
                this.Factory.AddParameter(cmd, "@clientTypeId", SqlDbType.Int, 10, clientTypeId);

             
                  
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);



                if ((relationStart == null) || (relationStart.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationStart", SqlDbType.DateTime, 10, relationStart);

                if ((relationEnd == null) || (relationEnd.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@relationEnd", SqlDbType.DateTime, 10, relationEnd);


                if ((updateTime == null) || (updateTime.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@updateDate", SqlDbType.DateTime, 10, relationEnd);
                this.Factory.ExecuteNonQuery(cmd);


        

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


        

    }
}

